package org.tamal.tools;

import java.awt.GraphicsEnvironment;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.Calendar;
import java.util.Date;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * This class generates Excel document.
 * @author Tamal Kanti Nath
 */
public final class ExcelDemo {
    private Workbook wb = new XSSFWorkbook();

    private void alignmentDemo() {
        Sheet sheet = wb.createSheet("Alignment");
        for (Map.Entry<String, Short> vertical : getCellStyles("VERTICAL.*")
                .entrySet()) {
            sheet.createRow(vertical.getValue());
            for (Map.Entry<String, Short> align : getCellStyles("ALIGN.*")
                    .entrySet()) {
                Cell cell = sheet.getRow(vertical.getValue()).createCell(
                        align.getValue());
                CellStyle style = wb.createCellStyle();
                style.setAlignment(align.getValue());
                style.setVerticalAlignment(vertical.getValue());
                cell.setCellStyle(style);
                cell.setCellValue("(" + align.getKey().substring(6) + ", "
                        + vertical.getKey().substring(9) + ")");
            }
        }
        for (int i = 0; i < sheet.getRow(0).getLastCellNum(); ++i) {
            sheet.setColumnWidth(i, 256 * 25);
        }
        for (int i = 0; i < sheet.getLastRowNum(); ++i) {
            sheet.getRow(i).setHeightInPoints(40);
        }
        Row row = sheet.createRow(sheet.getLastRowNum() + 1);
        for (short i = 0; i <= 90; i += 15) {
            Cell cell = row.createCell(i / 15);
            CellStyle style = wb.createCellStyle();
            style.setRotation(i);
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            cell.setCellStyle(style);
            cell.setCellValue("Rotation: " + i);
        }
    }

    private void borderDemo() {
        Sheet sheet = wb.createSheet("Border");
        sheet.setDisplayGridlines(false);
        for (Map.Entry<String, Short> border : getCellStyles("BORDER.*")
                .entrySet()) {
            CellStyle style = wb.createCellStyle();
            short i = border.getValue();
            style.setBorderLeft(i);
            style.setBorderRight(i);
            style.setBorderTop(i);
            style.setBorderBottom(i);
            sheet.createRow(i).createCell(i % 4).setCellStyle(style);
            sheet.getRow(i).getCell(i % 4).setCellValue(border.getKey());
        }
        for (int i = 0; i < 4; ++i) {
            sheet.autoSizeColumn(i);
        }
    }

    private void colorDemo() {
        Sheet sheet = wb.createSheet("Color");
        sheet.createRow(0).createCell(0).setCellValue("Font Color");
        sheet.getRow(0).createCell(1).setCellValue("Border Color");
        sheet.getRow(0).createCell(2).setCellValue("Fill Color");

        for (IndexedColors c: IndexedColors.values()) {
            Cell cell = sheet.createRow(sheet.getLastRowNum() + 1)
                    .createCell(0);
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setColor(c.getIndex());
            style.setFont(font);
            cell.setCellStyle(style);
            cell.setCellValue(c.name());
            cell = sheet.getRow(sheet.getLastRowNum()).createCell(1);
            style = wb.createCellStyle();
            style.setLeftBorderColor(c.getIndex());
            style.setRightBorderColor(c.getIndex());
            style.setTopBorderColor(c.getIndex());
            style.setBottomBorderColor(c.getIndex());
            style.setBorderLeft(CellStyle.BORDER_MEDIUM);
            style.setBorderRight(CellStyle.BORDER_MEDIUM);
            style.setBorderTop(CellStyle.BORDER_MEDIUM);
            style.setBorderBottom(CellStyle.BORDER_MEDIUM);
            cell.setCellStyle(style);
            cell.setCellValue(c.ordinal());
            cell = sheet.getRow(sheet.getLastRowNum()).createCell(2);
            style = wb.createCellStyle();
            style.setFillForegroundColor(c.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            cell.setCellStyle(style);
            cell.setCellValue(c.ordinal());
        }
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
    }

    private void fillDemo() {
        Sheet sheet = wb.createSheet("Fill");
        Map<String, Short> temp = getCellStyles("(ALIGN|VERTICAL|BORDER).*");
        Map<String, Short> fill = getCellStyles(".*");
        for (String key : temp.keySet()) {
            fill.remove(key);
        }
        sheet.createRow(0).createCell(0).setCellValue("Fill Type");
        sheet.getRow(0).createCell(1).setCellValue("Example");
        for (Map.Entry<String, Short> entry: fill.entrySet()) {
            sheet.createRow(sheet.getLastRowNum() + 1).createCell(0)
                    .setCellValue(entry.getKey());
            CellStyle style = wb.createCellStyle();
            style.setFillPattern(entry.getValue());
            style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            style.setFillBackgroundColor(IndexedColors.RED.getIndex());
            sheet.getRow(sheet.getLastRowNum()).createCell(1)
                    .setCellStyle(style);
        }
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
    }

    private void fontDemo() {
        Sheet sheet = wb.createSheet("Font");
        sheet.createRow(0).createCell(0).setCellValue("-- Fonts --");
        for (String name : GraphicsEnvironment.getLocalGraphicsEnvironment()
                .getAvailableFontFamilyNames()) {
            int i = sheet.getLastRowNum() + 1;
            Cell cell = sheet.createRow(i).createCell(0);
            Font font = wb.createFont();
            font.setFontName(name);
            CellStyle style = wb.createCellStyle();
            style.setFont(font);
            cell.setCellStyle(style);
            cell.setCellValue(name);
        }
        sheet.autoSizeColumn(0);
    }

    private void fontStyleDemo() {
        Sheet sheet = wb.createSheet("Font Style");
        sheet.createRow(0).createCell(0).setCellValue("Font Name");
        sheet.getRow(0).createCell(1).setCellValue("Normal");
        sheet.getRow(0).createCell(2).setCellValue("Bold");
        sheet.getRow(0).createCell(3).setCellValue("Italic");
        sheet.getRow(0).createCell(4).setCellValue("Underline (single)");
        sheet.getRow(0).createCell(5).setCellValue("Underline (double)");
        sheet.getRow(0).createCell(6).setCellValue(
                "Underline (single accounting)");
        sheet.getRow(0).createCell(7).setCellValue(
                "Underline (double accounting)");
        sheet.getRow(0).createCell(8).setCellValue("Strikeout");
        sheet.getRow(0).createCell(9).setCellValue("Superscript");
        sheet.getRow(0).createCell(10).setCellValue("Subscript");
        sheet.getRow(0).createCell(11).setCellValue("Height");
        Set<String> fonts = new TreeSet<>();
        for (java.awt.Font font
            : GraphicsEnvironment.getLocalGraphicsEnvironment().getAllFonts()) {
            if (font.canDisplay('\u0995')) {
                fonts.add(font.getFamily());
            }
        }
        for (String f: fonts) {
            int i = sheet.getLastRowNum() + 1;
            sheet.createRow(i).createCell(0).setCellValue(f);
            Row row = sheet.getRow(sheet.getLastRowNum());
            Font font = createFont(f);
            font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setItalic(true);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setUnderline(Font.U_SINGLE);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setUnderline(Font.U_DOUBLE);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setUnderline(Font.U_SINGLE_ACCOUNTING);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setUnderline(Font.U_DOUBLE_ACCOUNTING);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setStrikeout(true);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setTypeOffset(Font.SS_SUPER);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setTypeOffset(Font.SS_SUB);
            createFontStyleCell(font, row);
            font = createFont(f);
            font.setFontHeightInPoints((short) 20);
            createFontStyleCell(font, row);
        }
        for (int i = 0; i < 12; ++i) {
            sheet.autoSizeColumn(i);
        }
    }

    private void formatCells() {
        Sheet sheet = wb.createSheet("Format Cells");
        DataFormat df = wb.createDataFormat();
        for (short i = 0; i < 50; ++i) {
            CellStyle style = wb.createCellStyle();
            String format = df.getFormat(i);
            Row row = sheet.createRow(i);
            style.setDataFormat(i);
            row.setRowStyle(style);
            row.createCell(0).setCellValue(format);
            row.createCell(1).setCellValue(true);
            row.createCell(2).setCellValue(Calendar.getInstance());
            row.createCell(3).setCellValue(new Date());
            row.createCell(4).setCellValue(41234.56789);
            row.createCell(5).setCellValue("A String");
            for (short j = 1; j <= 5; ++j) {
                row.getCell(j).setCellStyle(style);
            }
        }
        for (short i = 0; i <= 5; ++i) {
            sheet.autoSizeColumn(i);
        }
    }

    private Font createFont(String fontName) {
        Font font = wb.createFont();
        font.setFontName(fontName);
        return font;
    }

    private void createFontStyleCell(Font font, Row row) {
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        Cell cell = row.createCell(row.getLastCellNum());
        cell.setCellValue("তমাল কান্তি নাখ");
        cell.setCellStyle(style);
    }

    private static Map<String, Short> getCellStyles(String regex) {
        Map<String, Short> styles = new TreeMap<>();
        for (Field field: CellStyle.class.getDeclaredFields()) {
            if (field.getName().matches(regex)) {
                try {
                    styles.put(field.getName(), field.getShort(null));
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return styles;
    }

    private void save(String file) {
        try (OutputStream os = new FileOutputStream(file)) {
            wb.write(os);
            os.close();
            if ("Linux".equals(System.getProperty("os.name"))) {
                Runtime.getRuntime().exec(new String[] {"gnome-open", file});
            } else {
                Runtime.getRuntime().exec(
                        new String[] {"explorer", file.replace('/', '\\')});
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Starting point.
     * @param args arguments
     */
    public static void main(String[] args) {
        String file = "/tmp/poi.xlsx";
        ExcelDemo demo = new ExcelDemo();
        demo.alignmentDemo();
        demo.borderDemo();
        demo.colorDemo();
        demo.fillDemo();
        demo.fontDemo();
        demo.fontStyleDemo();
        demo.formatCells();
        demo.save(file);
    }
}
